/*
This will retrieve all warning and expiration notices due for Content Management (CON and NAV items)

After retrieving the notices due, it will update the workflow tables to show that the notices have been sent
*/

CREATE PROC [dbo].[asi_GetCMNotificationsDue]
AS
BEGIN

    declare
    @navWarningDays1 int,
    @navWarningDays2 int,
    @navFinalNoticeDays int,
    @conWarningDays1 int,
    @conWarningDays2 int,
    @conFinalNoticeDays int,
    @conAddNoticeDays int,
    @navUseWarning1 bit,
    @navUseWarning2 bit,
    @navUseFinalNotice bit,
    @conUseWarning1 bit,
    @conUseWarning2 bit,
    @conUseFinalNotice bit,
    @conUseAddNotice bit

    SELECT @navWarningDays1 = CONVERT(int, [ParameterValue]) FROM [dbo].[SystemConfig] WHERE [ParameterName] = 'ND.ExpirationWarningDays1'
    SELECT @navWarningDays2 = CONVERT(int, [ParameterValue]) FROM [dbo].[SystemConfig] WHERE [ParameterName] = 'ND.ExpirationWarningDays2'
    SELECT @navFinalNoticeDays = CONVERT(int, [ParameterValue]) FROM [dbo].[SystemConfig] WHERE [ParameterName] = 'ND.FinalExpirationNoticeDays'
    SELECT @conWarningDays1 = CONVERT(int, [ParameterValue]) FROM [dbo].[SystemConfig] WHERE [ParameterName] = 'CM.ExpirationWarningDays1'
    SELECT @conWarningDays2 = CONVERT(int, [ParameterValue]) FROM [dbo].[SystemConfig] WHERE [ParameterName] = 'CM.ExpirationWarningDays2'
    SELECT @conFinalNoticeDays = CONVERT(int, [ParameterValue]) FROM [dbo].[SystemConfig] WHERE [ParameterName] = 'CM.FinalExpirationNoticeDays'
    SELECT @conAddNoticeDays = CONVERT(int, [ParameterValue]) FROM [dbo].[SystemConfig] WHERE [ParameterName] = 'CM.ExpAdditionalDays'

    SET @navUseWarning1 = 1
    SET @navUseWarning2 = 1
    SET @navUseFinalNotice = 1
    SET @conUseWarning1 = 1
    SET @conUseWarning2 = 1
    SET @conUseFinalNotice = 1
    SET @conUseAddNotice = 1

    IF @navWarningDays1 <= 0 BEGIN SET @navUseWarning1 = 0 END
    IF @navWarningDays2 <= 0 BEGIN SET @navUseWarning2 = 0 END
    IF @navWarningDays1 = @navWarningDays2 BEGIN SET @navUseWarning2 = 0 END
    IF @navFinalNoticeDays <= 0 BEGIN SET @navUseFinalNotice = 0 END
    IF @conWarningDays1 <= 0 BEGIN SET @conUseWarning1 = 0 END
    IF @conWarningDays2 <= 0 BEGIN SET @conUseWarning2 = 0 END
    IF @conWarningDays1 = @conWarningDays2 BEGIN SET @conUseWarning2 = 0 END
    IF @conFinalNoticeDays <= 0 BEGIN SET @conUseFinalNotice = 0 END
    IF @conAddNoticeDays <= 0 BEGIN SET @conUseAddNotice = 0 END

    DECLARE @warnings TABLE (
        DocumentVersionKey uniqueidentifier,
        DocumentTypeCode nvarchar(3),
        ContactKey uniqueidentifier,
        Email nvarchar(100),
        WarningNumber int,
        NoticeNumber int
    )

    IF @navUseWarning1 = 1
    BEGIN

        -- Get navigations that need 1st warning
        INSERT @warnings (
            [DocumentVersionKey],
            [DocumentTypeCode],
            [ContactKey],
            [WarningNumber],
            [NoticeNumber])
        SELECT
            nav.[DocumentVersionKey],
            'NAV',
            nav.[NotifyContactKey],
            1,
            0
        FROM [dbo].[NavigationWorkflowParameters] nav
            INNER JOIN [dbo].[DocumentMain] doc ON nav.[DocumentVersionKey] = doc.[DocumentVersionKey]
        WHERE
            nav.[RemoveAfterDays] > 0
            AND doc.[DocumentStatusCode] = 40
            AND nav.[FirstWarningSentOn] IS NULL
            AND DATEADD(day, nav.[RemoveAfterDays] - @navWarningDays1, CONVERT(datetime, CONVERT(char, doc.[StatusUpdatedOn], 103), 103))
                <= CONVERT(datetime, CONVERT(char, GETDATE(), 103), 103)

    END

    IF @navUseWarning2 = 1
    BEGIN

        -- Get navigations that need 2nd warning
        INSERT @warnings (
            [DocumentVersionKey],
            [DocumentTypeCode],
            [ContactKey],
            [WarningNumber],
            [NoticeNumber])
        SELECT
            nav.[DocumentVersionKey],
            'NAV',
            nav.[NotifyContactKey],
            2,
            0
        FROM [dbo].[NavigationWorkflowParameters] nav
            INNER JOIN [dbo].[DocumentMain] doc ON nav.[DocumentVersionKey] = doc.[DocumentVersionKey]
        WHERE
            nav.[RemoveAfterDays] > 0
            AND doc.[DocumentStatusCode] = 40
            AND nav.[SecondWarningSentOn] IS NULL
            AND DATEDIFF(hour, nav.[FirstWarningSentOn], GETDATE()) >= 20
            AND DATEADD(day, nav.[RemoveAfterDays] - @navWarningDays2, CONVERT(datetime, CONVERT(char, doc.[StatusUpdatedOn], 103), 103))
                <= CONVERT(datetime, CONVERT(char, GETDATE(), 103), 103)


    END

        -- Get navigation to inactivate and send first notice
        INSERT @warnings (
            [DocumentVersionKey],
            [DocumentTypeCode],
            [ContactKey],
            [WarningNumber],
            [NoticeNumber])
        SELECT
            nav.[DocumentVersionKey],
            'NAV',
            nav.[NotifyContactKey],
            0,
            1
        FROM [dbo].[NavigationWorkflowParameters] nav
            INNER JOIN [dbo].[DocumentMain] doc ON nav.[DocumentVersionKey] = doc.[DocumentVersionKey]
        WHERE
            nav.[RemoveAfterDays] > 0
            AND doc.[DocumentStatusCode] = 40
            AND nav.[FirstNotificationSentOn] IS NULL
            AND ((@navUseWarning2 = 1 AND DATEDIFF(hour, nav.[SecondWarningSentOn], GETDATE()) >= 20)
                OR (@navUseWarning2 = 0 and DATEDIFF(hour, nav.[FirstWarningSentOn], GETDATE()) >= 20))
            AND DATEADD(day, nav.[RemoveAfterDays], CONVERT(datetime, CONVERT(char, doc.[StatusUpdatedOn], 103), 103))
                <= CONVERT(datetime, CONVERT(char, GETDATE(), 103), 103)

    IF @navUseFinalNotice = 1
    BEGIN
        -- Get navigation to send second notice
        INSERT @warnings (
            [DocumentVersionKey],
            [DocumentTypeCode],
            [ContactKey],
            [WarningNumber],
            [NoticeNumber])
        SELECT
            nav.[DocumentVersionKey],
            'NAV',
            nav.[NotifyContactKey],
            0,
            2
        FROM [dbo].[NavigationWorkflowParameters] nav
            INNER JOIN [dbo].[DocumentMain] doc ON nav.[DocumentVersionKey] = doc.[DocumentVersionKey]
        WHERE
            nav.[RemoveAfterDays] > 0
            AND doc.[DocumentStatusCode] = 40
            AND nav.[SecondNotificationSentOn] IS NULL
            AND DATEDIFF(hour, nav.[FirstNotificationSentOn], GETDATE()) >= 20
            AND DATEADD(day, nav.[RemoveAfterDays] + @navFinalNoticeDays, CONVERT(datetime, CONVERT(char, doc.[StatusUpdatedOn], 103), 103))
                <= CONVERT(datetime, CONVERT(char, GETDATE(), 103), 103)
    END

    IF @conUseWarning1 = 1
    BEGIN

        -- Get content that need 1st warning
        INSERT @warnings (
            [DocumentVersionKey],
            [DocumentTypeCode],
            [ContactKey],
            [WarningNumber],
            [NoticeNumber])
        SELECT
            con.[DocumentVersionKey],
            'CON',
            gm.[MemberContactKey],
            1,
            0
        FROM [dbo].[ContentWorkflowParameters] con
            INNER JOIN [dbo].[DocumentMain] doc ON con.[DocumentVersionKey] = doc.[DocumentVersionKey]
            INNER JOIN [dbo].[GroupMember] gm ON con.[OwnerGroupMemberKey] = gm.[GroupMemberKey]
        WHERE
            doc.[DocumentTypeCode] = 'CON'
            AND doc.[DocumentStatusCode] IN (40,60)
            AND con.[PublishedVersion] = 1
            AND con.[FirstWarningSentOn] IS NULL
            AND DATEADD(day, - @conWarningDays1, CONVERT(datetime, CONVERT(char, con.[ExpirationDate], 103), 103))
                <= CONVERT(datetime, CONVERT(char, GETDATE(), 103), 103)

    END

    IF @conUseWarning2 = 1
    BEGIN

        -- Get content that need 2nd warning
        INSERT @warnings (
            [DocumentVersionKey],
            [DocumentTypeCode],
            [ContactKey],
            [WarningNumber],
            [NoticeNumber])
        SELECT
            con.[DocumentVersionKey],
            'CON',
            gm.[MemberContactKey],
            2,
            0
        FROM [dbo].[ContentWorkflowParameters] con
            INNER JOIN [dbo].[DocumentMain] doc ON con.[DocumentVersionKey] = doc.[DocumentVersionKey]
            INNER JOIN [dbo].[GroupMember] gm ON con.[OwnerGroupMemberKey] = gm.[GroupMemberKey]
        WHERE
            doc.[DocumentTypeCode] = 'CON'
            AND doc.[DocumentStatusCode] IN (40,60)
            AND con.[PublishedVersion] = 1
            AND con.[SecondWarningSentOn] IS NULL
            AND DATEDIFF(hour, con.[FirstWarningSentOn], GETDATE()) >= 20
            AND DATEADD(day, - @conWarningDays2, CONVERT(datetime, CONVERT(char, con.[ExpirationDate], 103), 103))
                <= CONVERT(datetime, CONVERT(char, GETDATE(), 103), 103)


    END

        -- Get content to inactivate and send first notice
        INSERT @warnings (
            [DocumentVersionKey],
            [DocumentTypeCode],
            [ContactKey],
            [WarningNumber],
            [NoticeNumber])
        SELECT
            con.[DocumentVersionKey],
            'CON',
            gm.[MemberContactKey],
            0,
            1
        FROM [dbo].[ContentWorkflowParameters] con
            INNER JOIN [dbo].[DocumentMain] doc ON con.[DocumentVersionKey] = doc.[DocumentVersionKey]
            INNER JOIN [dbo].[GroupMember] gm ON con.[OwnerGroupMemberKey] = gm.[GroupMemberKey]
        WHERE
            doc.[DocumentTypeCode] = 'CON'
            AND doc.[DocumentStatusCode] IN (40,60)
            AND con.[PublishedVersion] = 1
            AND con.[FirstNotificationSentOn] IS NULL
            AND ((@conUseWarning2 = 1 AND DATEDIFF(hour, con.[SecondWarningSentOn], GETDATE()) >= 20)
                OR (@conUseWarning2 = 0 and DATEDIFF(hour, con.[FirstWarningSentOn], GETDATE()) >= 20))
            AND CONVERT(datetime, CONVERT(char, con.[ExpirationDate], 103), 103)
                <= CONVERT(datetime, CONVERT(char, GETDATE(), 103), 103)

    IF @conUseFinalNotice = 1
    BEGIN
        -- Get content to send second notice
        INSERT @warnings (
            [DocumentVersionKey],
            [DocumentTypeCode],
            [ContactKey],
            [WarningNumber],
            [NoticeNumber])
        SELECT
            con.[DocumentVersionKey],
            'CON',
            gm.[MemberContactKey],
            0,
            2
        FROM [dbo].[ContentWorkflowParameters] con
            INNER JOIN [dbo].[DocumentMain] doc ON con.[DocumentVersionKey] = doc.[DocumentVersionKey]
            INNER JOIN [dbo].[GroupMember] gm ON con.[OwnerGroupMemberKey] = gm.[GroupMemberKey]
        WHERE
            doc.[DocumentTypeCode] = 'CON'
            AND doc.[DocumentStatusCode] IN (40,60)
            AND con.[PublishedVersion] = 1
            AND con.[SecondNotificationSentOn] IS NULL
            AND DATEDIFF(hour, con.[FirstNotificationSentOn], GETDATE()) >= 20
            AND DATEADD(day, @conFinalNoticeDays, CONVERT(datetime, CONVERT(char, con.[ExpirationDate], 103), 103))
                <= CONVERT(datetime, CONVERT(char, GETDATE(), 103), 103)
    END

    IF @conUseAddNotice = 1
    BEGIN

        -- Get content to send additional notice
        INSERT @warnings (
            [DocumentVersionKey],
            [DocumentTypeCode],
            [ContactKey],
            [WarningNumber],
            [NoticeNumber])
        SELECT
            con.[DocumentVersionKey],
            'CON',
            gm.[MemberContactKey],
            0,
            3
        FROM [dbo].[ContentWorkflowParameters] con
            INNER JOIN [dbo].[DocumentMain] doc ON con.[DocumentVersionKey] = doc.[DocumentVersionKey]
            INNER JOIN [dbo].[GroupMember] gm ON con.[OwnerGroupMemberKey] = gm.[GroupMemberKey]
        WHERE
            doc.[DocumentTypeCode] = 'CON'
            AND doc.[DocumentStatusCode] IN (40,60)
            AND con.[PublishedVersion] = 1
            AND con.[AdditionalNoticeSentOn] IS NULL
            AND ((@conUseFinalNotice = 1 AND DATEDIFF(hour, con.[SecondNotificationSentOn], GETDATE()) >= 20)
                OR (@conUseFinalNotice = 0 and DATEDIFF(hour, con.[FirstNotificationSentOn], GETDATE()) >= 20))
            AND DATEADD(day, @conAddNoticeDays, CONVERT(datetime, CONVERT(char, con.[ExpirationDate], 103), 103))
                <= CONVERT(datetime, CONVERT(char, GETDATE(), 103), 103)

    END

    -- Get email addresses
    UPDATE @warnings
    SET
        [Email] = n.[EMAIL]
    FROM (@warnings temp INNER JOIN [dbo].[ContactMain] c
            ON temp.[ContactKey] = c.[ContactKey])
        INNER JOIN [dbo].[Name] n ON c.[SyncContactID] = n.[ID]

    ----------------------------------
    -- Begin Update Workflow Tables --
    ----------------------------------

    UPDATE [dbo].[NavigationWorkflowParameters]
    SET [FirstWarningSentOn] = GETDATE()
    WHERE [DocumentVersionKey] in (
        Select [DocumentVersionKey]
        FROM @warnings
        WHERE [WarningNumber] = 1
            AND [DocumentTypeCode] = 'NAV'
            AND ISNULL([Email],'') != '')

    UPDATE [dbo].[NavigationWorkflowParameters]
    SET [SecondWarningSentOn] = GETDATE()
    WHERE [DocumentVersionKey] in (
        Select [DocumentVersionKey]
        FROM @warnings
        WHERE [WarningNumber] = 2
            AND [DocumentTypeCode] = 'NAV'
            AND ISNULL([Email],'') != '')

    UPDATE [dbo].[NavigationWorkflowParameters]
    SET [FirstNotificationSentOn] = GETDATE()
    WHERE [DocumentVersionKey] in (
        Select [DocumentVersionKey]
        FROM @warnings
        WHERE [NoticeNumber] = 1
            AND [DocumentTypeCode] = 'NAV'
            AND ISNULL([Email],'') != '')

    UPDATE [dbo].[NavigationWorkflowParameters]
    SET [SecondNotificationSentOn] = GETDATE()
    WHERE [DocumentVersionKey] in (
        Select [DocumentVersionKey]
        FROM @warnings
        WHERE [NoticeNumber] = 2
            AND [DocumentTypeCode] = 'NAV'
            AND ISNULL([Email],'') != '')

    UPDATE [dbo].[ContentWorkflowParameters]
    SET [FirstWarningSentOn] = GETDATE()
    WHERE [DocumentVersionKey] in (
        Select [DocumentVersionKey]
        FROM @warnings
        WHERE [WarningNumber] = 1
            AND [DocumentTypeCode] = 'CON'
            AND ISNULL([Email],'') != '')

    UPDATE [dbo].[ContentWorkflowParameters]
    SET [SecondWarningSentOn] = GETDATE()
    WHERE [DocumentVersionKey] in (
        Select [DocumentVersionKey]
        FROM @warnings
        WHERE [WarningNumber] = 2
            AND [DocumentTypeCode] = 'CON'
            AND ISNULL([Email],'') != '')

    UPDATE [dbo].[ContentWorkflowParameters]
    SET [FirstNotificationSentOn] = GETDATE()
    WHERE [DocumentVersionKey] in (
        Select [DocumentVersionKey]
        FROM @warnings
        WHERE [NoticeNumber] = 1
            AND [DocumentTypeCode] = 'CON'
            AND ISNULL([Email],'') != '')

    UPDATE [dbo].[ContentWorkflowParameters]
    SET [SecondNotificationSentOn] = GETDATE()
    WHERE [DocumentVersionKey] in (
        Select [DocumentVersionKey]
        FROM @warnings
        WHERE [NoticeNumber] = 2
            AND [DocumentTypeCode] = 'CON'
            AND ISNULL([Email],'') != '')

    UPDATE [dbo].[ContentWorkflowParameters]
    SET [AdditionalNoticeSentOn] = GETDATE()
    WHERE [DocumentVersionKey] in (
        Select [DocumentVersionKey]
        FROM @warnings
        WHERE [NoticeNumber] = 3
            AND [DocumentTypeCode] = 'CON'
            AND ISNULL([Email],'') != '')

    --------------------------------
    -- End Update Workflow Tables --
    --------------------------------


    SELECT temp.*, h.[HierarchyKey] FROM @warnings temp INNER JOIN [dbo].[Hierarchy] h
        ON temp.[DocumentVersionKey] = h.[UniformKey]
    ORDER BY
        temp.[DocumentTypeCode],
        temp.[ContactKey],
        temp.[NoticeNumber],
        temp.[WarningNumber]
        

END

GO